﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SWDataDB.DAL;
using SWDataDB.Models;
using System.Data.SqlClient;

namespace SWDataDB.DAL
{
    /// <summary>
    ///  剖面信息
    ///  2013-05-19:zhhw
    /// </summary>

    public class PMInfo
    {
        public PMInfo() { }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回dataSet</returns>
        public DataSet GetAllInfoBySet()
        {
            string strSql = "SELECT * FROM dbo.PM_TBL";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 获取所有信息
        /// </summary>
        /// <returns>返回datatable</returns>
        public DataTable GetAllInfoByTable()
        {
            string strSql = "SELECT * FROM dbo.PM_TBL";
            return SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到记录for 分页显示，升序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByASC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID ASC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 得到著录表记录for 分页显示，降序排列
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strExp"></param>
        /// <returns>返回dataset</returns>
        public DataSet GetInfoForPageByDESC(string strExp)
        {
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE ";
            strSql += strExp;
            strSql += "ORDER BY ID DESC";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 分页查询，后台控制页面大小
        /// </summary>
        /// <param name="page"></param>
        /// <param name="num"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public IList<PM_TBL> GetInfoByPage(long page, long num, string strWhere)
        {
            //日期和ID没写
            string strSql = SQLHelper.GetPagingSQL("dbo.PM_TBL", "ID", "*", strWhere, "ID DESC", num, page);
            IList<PM_TBL> pmLst = new List<PM_TBL>();

            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    PM_TBL pm_info = new PM_TBL();
                    pm_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    pm_info.O_PM_NAME = dr["PM_NAME"].ToString();
                    pm_info.O_PM_CODE = dr["PM_CODE"].ToString();

                    if (dr["BB_NUM"].ToString() != "")
                        pm_info.O_BB_NUM = int.Parse(dr["BB_NUM"].ToString());

                    if (dr["YP_NUM"].ToString() != "")
                        pm_info.O_YP_NUM = int.Parse(dr["YP_NUM"].ToString());

                    if (dr["GP_NUM"].ToString() != "")
                        pm_info.O_GP_NUM = int.Parse(dr["GP_NUM"].ToString());

                    if (dr["BOP_NUM"].ToString() != "")
                        pm_info.O_BOP_NUM = int.Parse(dr["BOP_NUM"].ToString());

                    pm_info.O_PM_QUALITY = dr["PM_QUALITY"].ToString();

                    if (dr["SCALE"].ToString() != "")
                        pm_info.O_SCALE = dr["SCALE"].ToString();

                    if (dr["PM_SLON"].ToString() != "")
                        pm_info.O_PM_SLON = Convert.ToInt32(dr["PM_SLON"].ToString());
                    if (dr["PM_SLAT"].ToString() != "")
                        pm_info.O_PM_SLAT = Convert.ToInt32(dr["PM_SLAT"].ToString());

                    pm_info.O_PM_SUMMARY = dr["PM_SUMMARY"].ToString();
                    pm_info.O_PM_LDES = dr["PM_LDES"].ToString();
                    pm_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    pm_info.O_TEST_PEOPLE = dr["TEST_PEOPLE"].ToString();


                    pm_info.O_PM_DATE = dr["PM_DATE"].ToString();

                    pmLst.Add(pm_info);
                }

                return pmLst;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过fileID查询记录
        /// </summary>
        /// <param name="fileId"></param>
        /// <returns></returns>
        public PM_TBL SelectPMInfoByID(string pm_code)
        {
            PM_TBL pm_info = new PM_TBL();
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE PM_CODE ='";
            strSql += pm_code + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    pm_info.O_ID = int.Parse(dr["ID"].ToString());
                    pm_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    pm_info.O_PM_NAME = dr["PM_NAME"].ToString();
                    pm_info.O_PM_CODE = dr["PM_CODE"].ToString();
                    pm_info.O_BB_NUM = int.Parse(dr["BB_NUM"].ToString());
                    pm_info.O_YP_NUM = int.Parse(dr["YP_NUM"].ToString());
                    pm_info.O_GP_NUM = int.Parse(dr["GP_NUM"].ToString());
                    pm_info.O_BOP_NUM = int.Parse(dr["BOP_NUM"].ToString());
                    pm_info.O_PM_QUALITY = dr["PM_QUALITY"].ToString();
                    pm_info.O_SCALE = dr["SCALE"].ToString();
                    pm_info.O_PM_SLON = int.Parse(dr["PM_SLON"].ToString());
                    pm_info.O_PM_SLAT = int.Parse(dr["PM_SLAT"].ToString());
                    pm_info.O_PM_SUMMARY = dr["PM_SUMMARY"].ToString();
                    pm_info.O_PM_LDES = dr["PM_LDES"].ToString();
                    pm_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    pm_info.O_TEST_PEOPLE = dr["TEST_PEOPLE"].ToString();
                    pm_info.O_PM_DATE = dr["PM_DATE"].ToString();
                }

                return pm_info;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 通过name查询记录
        /// </summary>
        /// <param name="pm_name"></param>
        /// <returns></returns>
        public PM_TBL SelectPMInfoByName(string pm_name)
        {
            PM_TBL pm_info = new PM_TBL();
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE GP_NAME ='";
            strSql += pm_name + "'";
            try
            {
                DataTable dt = SQLHelper.ExecuteReaderDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
                foreach (DataRow dr in dt.Rows)
                {
                    pm_info.O_ID = int.Parse(dr["ID"].ToString());
                    pm_info.O_ZL_FILE_ID = dr["ZL_FILE_ID"].ToString();
                    pm_info.O_PM_NAME = pm_name;
                    pm_info.O_PM_CODE = dr["PM_CODE"].ToString();
                    pm_info.O_BB_NUM = int.Parse(dr["BB_NUM"].ToString());
                    pm_info.O_YP_NUM = int.Parse(dr["YP_NUM"].ToString());
                    pm_info.O_GP_NUM = int.Parse(dr["GP_NUM"].ToString());
                    pm_info.O_BOP_NUM = int.Parse(dr["BOP_NUM"].ToString());
                    pm_info.O_PM_QUALITY = dr["PM_QUALITY"].ToString();
                    pm_info.O_SCALE = dr["SCALE"].ToString();
                    pm_info.O_PM_SLON = int.Parse(dr["PM_SLON"].ToString());
                    pm_info.O_PM_SLAT = int.Parse(dr["PM_SLAT"].ToString());
                    pm_info.O_PM_SUMMARY = dr["PM_SUMMARY"].ToString();
                    pm_info.O_PM_LDES = dr["PM_LDES"].ToString();
                    pm_info.O_KEY_WORDS = dr["KEY_WORDS"].ToString();
                    pm_info.O_TEST_PEOPLE = dr["TEST_PEOPLE"].ToString();
                    pm_info.O_PM_DATE = dr["PM_DATE"].ToString();
                }

                return pm_info;
            }
            catch (ArgumentNullException e)
            {
                throw e;
            }
            catch (FormatException e)
            {
                throw e;
            }
            catch (OverflowException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 根据外键查询
        /// </summary>
        /// <param name="zl_file_id"></param>
        /// <returns></returns>
        public DataSet SelectInfoByFileID(string zl_file_id)
        {
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "'";

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        public DataSet SelectInfoByZLIDPMCODE(string zl_file_id,string pmcode)
        {
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE ZL_FILE_ID ='";
            strSql += zl_file_id + "' and PM_CODE='";
            strSql += pmcode + "'";
            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        //根据ID获得该条数据

        public DataSet SelectInfoByID(int PMID)
        {
            string strSql = "SELECT * FROM dbo.PM_TBL WHERE ID =";
            strSql += PMID;

            return SQLHelper.ExecuteDataSet(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }


        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="pm_info"></param>
        /// <returns></returns>
        public int AddPMInfo(PM_TBL pm_info)
        {
            // 先处理普通数据
            string strSql = "INSERT INTO dbo.PM_TBL(ZL_FILE_ID,PM_NAME,PM_CODE,BB_NUM,YP_NUM,";
            strSql += "GP_NUM,BOP_NUM,PM_QUALITY,SCALE,PM_SLON,PM_SLAT,PM_SUMMARY,";
            strSql += "PM_LDES,KEY_WORDS,TEST_PEOPLE,PM_DATE) VALUES(";
            strSql += "'" + pm_info.O_ZL_FILE_ID + "',";
            strSql += "'" + pm_info.O_PM_NAME + "',";
            strSql += "'" + pm_info.O_PM_CODE + "',";
            strSql += pm_info.O_BB_NUM.ToString() + ",";
            strSql += pm_info.O_YP_NUM.ToString() + ",";
            strSql += pm_info.O_GP_NUM.ToString() + ",";
            strSql += "'" + pm_info.O_BOP_NUM + "',";
            strSql += "'" + pm_info.O_PM_QUALITY + "',";
            strSql += "'" + pm_info.O_SCALE.ToString() + "',";
            strSql += pm_info.O_PM_SLON.ToString() + ",";
            strSql += pm_info.O_PM_SLAT.ToString() + ",";
            strSql += "'" + pm_info.O_PM_SUMMARY + "',";
            strSql += "'" + pm_info.O_PM_LDES + "',";
            strSql += "'" + pm_info.O_KEY_WORDS + "',";
            strSql += "'" + pm_info.O_TEST_PEOPLE + "',";
            strSql += "'" + pm_info.O_PM_DATE.ToString() + "')";

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        /// <summary>
        /// 通过ID删除记录
        /// </summary>
        /// <param name="file_id"></param>
        /// <returns></returns>
        public int DeletePMInfoById(int id)
        {
            string strSql = "DELETE FROM dbo.PM_TBL WHERE ID =";
            strSql += id;

            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

//         /// <summary>
//         /// 通过名称删除记录
//         /// </summary>
//         /// <param name="pm_name"></param>
//         /// <returns></returns>
//         public int DeletePMInfoByName(string pm_name)
//         {
//             string strSql = "DELETE FROM dbo.PM_TBL WHERE YP_NAME ='";
//             strSql += pm_name + "'";
// 
//             return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
//         }

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="pm_info"></param>
        /// <returns></returns>
        public int UpdatePMInfo(PM_TBL pm_info)
        {
            string strSql = "UPDATE dbo.PM_TBL ";
            strSql += " set ZL_FILE_ID='" + pm_info.O_ZL_FILE_ID + "',";
            strSql += " PM_NAME='" + pm_info.O_PM_NAME + "',";
            strSql += " PM_CODE='" + pm_info.O_PM_CODE + "',";
            strSql += " BB_NUM=" + pm_info.O_BB_NUM.ToString() + ",";
            strSql += " YP_NUM =" + pm_info.O_YP_NUM.ToString() + ",";
            strSql += " GP_NUM=" + pm_info.O_GP_NUM.ToString() + ",";
            strSql += " BOP_NUM='" + pm_info.O_BOP_NUM + "',";

            strSql += " PM_QUALITY='" + pm_info.O_PM_QUALITY + "',";
            strSql += " SCALE='" + pm_info.O_SCALE + "',";
            strSql += " PM_SLON=" + pm_info.O_PM_SLON.ToString() + ",";
            strSql += " PM_SLAT=" + pm_info.O_PM_SLAT.ToString() + ",";
            strSql += " PM_SUMMARY='" + pm_info.O_PM_SUMMARY + "',";

            strSql += " PM_LDES='" + pm_info.O_PM_LDES + "',";
            strSql += " KEY_WORDS='" + pm_info.O_KEY_WORDS + "',";
            strSql += " TEST_PEOPLE='" + pm_info.O_TEST_PEOPLE + "',";
            strSql += " PM_DATE='" + pm_info.O_PM_DATE + "'";
            strSql += "WHERE ID =" + pm_info.O_ID;
  
            return SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, strSql, null);
        }

        #region =影像数据操作=
        public int AppendImage(int id, byte[] bImg, long len)
        {
            string SQL = "UPDATE  dbo.PM_TBL SET IMAGE = @IMAGE WHERE ID=" + id.ToString();

            SqlParameter[] commandParameters = new SqlParameter[1];
            commandParameters[0] = new SqlParameter("@IMAGE", SqlDbType.Image);
            commandParameters[0].Value = bImg;

            try
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.SQLConnString, CommandType.Text, SQL, commandParameters);
            }
            catch (Exception ex)
            {
                throw ex;
            }


            return 1;
        }

        public byte[] GetImage(int id)
        {
            byte[] photo = null;
            string sql = "SELECT IMAGE FROM dbo.PM_TBL WHERE ID =" + id.ToString();
            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.SQLConnString, CommandType.Text, sql, null);//采用SqlDataReader的方法来读取数据

            if (reader.Read())
                photo = reader[0] as byte[];//将第0列的数据写入byte数组 

            reader.Close();                 //关闭reader
            return photo;
        }

        #endregion
    }
}
